﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DomainModel;
using Utils;
using WebUI.Utils;
using System.Data;

namespace WebUI.Models
{
    public class DesignersViewModel: BaseViewModel
    {
        private int _numPerPage = 15;

        public DesignersViewModel(): base()
        { }

        public DesignersViewModel(DesignerSearchParamViewModel searchParam)
            : base(new { SearchParam = searchParam})
        { }

        
        public Boolean AvailableForHire { get; set; }
        public string Location { get; set; }
        public string Skills { get; set; }
        public int Page { get; set; }

        //ind list
        public List<IndLogin> IndLoginList { get; set; }
 
        public int PreviousPage { get; set; }
        public int NextPage { get; set; }

        public DesignerSearchParamViewModel ParamForPrevious { get; set; }
        public DesignerSearchParamViewModel ParamForNext { get; set; }

        public int TotalCount { get; set; }
        public Boolean IsEmptySearchParam
        {
            get
            {
                if (string.IsNullOrEmpty(Location) && string.IsNullOrEmpty(Skills) && !AvailableForHire)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        protected override void SetVarsWhichDependOnDynamicParams(dynamic dynParams)
        {
            DesignerSearchParamViewModel SearchParam = dynParams.SearchParam;

            //for UI
            try 
            {
                if (!string.IsNullOrEmpty(SearchParam.Page))
                {
                    Page = Convert.ToInt32(SearchParam.Page);
                }
                else
                {
                    Page = 1;
                }
            }
            catch{
                Page = 1;
            }
            Location = SearchParam.Location;
            Skills = SearchParam.Skills;
            AvailableForHire = SearchParam.For_Hire == "on" ? true : false;

            //search database
            List<string> locationTerms = ViewModelUtils.ClearSearchTerm(Location);
            List<string> skillsTerms = ViewModelUtils.SplitSearchTerm(Skills, ",");
            string sql = BuildSqlString(locationTerms, skillsTerms, Page, AvailableForHire);
            List<Ind> indList = SqlUtilities.Query<Ind>(sql, null, CommandType.Text, null).ToList();
            if (indList != null && indList.Count > 0)
            {
                List<Login> loginList = GetLoginListByIDs(indList.Select(x => x.LoginID).ToList());
                List<Shot> shotList = GetShotListByIndIDs(indList.Select(x => x.IndID).ToList());

                IndLoginList = BuildIndLoginList(indList, loginList, shotList);
                foreach (var indLog in IndLoginList)
                {
                    indLog.Ind["AvatarUrl"] = ImageUtils.GetAvatarRelativeUrl(AvatarType.IndAvatar, indLog.Ind.IndID, AvatarSizeEnum.Avatar_80x80, indLog.Ind.AvatarFileExtension);

                    foreach (var shot in indLog.Ind.ShotList)
                    {
                        shot["ShotUrl"] = ImageUtils.GetShotRelativeUrl(indLog.Ind.IndID, shot.ShotID, ShotSizeEnum.Shot_90x60, shot.FileExtension);
                    }
                }
                //get next page
                int totalCount = IndLoginList != null && IndLoginList.Count > 0 ? IndLoginList[0].Ind.TotalCount : 0;
                TotalCount = totalCount;
                PreviousPage = CommonUtilities.GetPreviousPage(Page);
                NextPage = CommonUtilities.GetNextPage(totalCount, Page, _numPerPage);
                ParamForPrevious = SearchParam.Clone();
                ParamForPrevious.Page = PreviousPage.ToString();
                ParamForNext = SearchParam.Clone();
                ParamForNext.Page = NextPage.ToString();
            }
            else
            {
                IndLoginList = new List<IndLogin>();
            }

        }

        private string BuildSqlString(List<string> locationTerms, List<string> skillsTerms, int page, Boolean availableForHire)
        {
            int start = (page - 1) * _numPerPage + 1;
            int end = page * _numPerPage;
            string sqlTemplate = @"
                declare @TotalCount int

                select @TotalCount = count(*) from ind i {3}

                select t1.*, @TotalCount as totalcount from 
                (
                    select i.*, row_number() over (order by createdate desc) as row from ind i {0}     
                ) as t1
                where t1.row between {1} and {2} ";
            string skillClauseTemplate = @"
                join 
                (
                    select ris.indid from r_indskill ris
                    join skill s on ris.skillid = s.skillid
                    where {0} group by ris.indid having count(ris.indid) = {1}
                ) as t on t.indid = i.indid";

            string result = string.Empty;
            
            string whereClause = string.Empty;
            List<string> andList = new List<string>();
            andList.Add(" (dateadd(day, 1, i.createdate) <= getdate()) "); //at least one day
            andList.Add(" exists(select * from shot s where s.indid = i.indid) "); //must have shots
            if (availableForHire)
            {
                andList.Add(" (i.availableForHire=1) ");
            }
            if (locationTerms.Count > 0)
            {
                foreach (string location in locationTerms)
                {
                    andList.Add(" (i.location like '%" + location + "%') ");
                }
            }

            if (andList.Count > 0)
            {
                whereClause = " where " + string.Join(" and ",  andList);
            }

            string skillClause = string.Empty;
            if (skillsTerms.Count > 0)
            {
                List<string> orList = new List<string>();
                foreach (string skill in skillsTerms)
                {
                    string replacedSkill = skill.Replace("'", "''");
                    orList.Add(" (s.name = '" + replacedSkill + "')");
                }
                skillClause = string.Format(skillClauseTemplate, string.Join(" or ", orList), skillsTerms.Count);
            }

            whereClause = skillClause + whereClause;

            result = string.Format(sqlTemplate, whereClause, start, end, whereClause);
            return result;
        }

        private List<Login> GetLoginListByIDs(List<int> loginIDs)
        {
            string sql = @"select * from login where loginid in (" + string.Join(",", loginIDs) + ")";
            return SqlUtilities.Query<Login>(sql, null, CommandType.Text, null).ToList(); ;
        }

        private List<Shot> GetShotListByIndIDs(List<int> indIDs)
        {
            string sql = @"
                select * from (
		            select s.*, row_number() over (partition by s.indID order by s.createdate desc) as row from shot s 
                    where s.indid in ({0}) 
	            ) as t
	            where t.row < 4";
            sql = string.Format(sql, string.Join(",", indIDs));
            return SqlUtilities.Query<Shot>(sql, null, CommandType.Text, null).ToList();
        }

        private List<IndLogin> BuildIndLoginList(List<Ind> indList, List<Login> loginList, List<Shot> shotList)
        {
            List<IndLogin> indLoginList = new List<IndLogin>();
            foreach (var ind in indList)
            {
                var indLogin = new IndLogin();
                var login = loginList.Find(x => x.LoginID == ind.LoginID);
                ind.ShotList = shotList.FindAll(x => x.IndID == ind.IndID);
                indLogin.Login = login;
                indLogin.Ind = ind;
                indLoginList.Add(indLogin);
            }

            return indLoginList;
        }
        
    }
}